package com.xtxk.utils.sqlite;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Component;

@Component
public class SettingOperator implements IOperator {
	private static Logger log = Logger.getLogger(SettingOperator.class);

	public void createTable() {
		Connection conn = SqliteOperator.getConnection();
		Statement stat = null;
		try {
			stat = conn.createStatement();
			if (!isTableExist(SQLiteInfo.TABLE_NAME)) {
				System.out.println("Create_YWSETTING_INFO_TABLE");
				stat.executeUpdate("CREATE TABLE " + SQLiteInfo.TABLE_NAME
						+ "(" + SQLiteInfo.CENTER_IP + " STRING, "
						+ SQLiteInfo.CENTER_PORT + " STRING,"+ SQLiteInfo.LINK_TYPE + " STRING,"
						+ SQLiteInfo.OPEN_ENCRYPTION + " INTEGER,"+ SQLiteInfo.AGREEMENT_TYPE + " INTEGER,"
						+ SQLiteInfo.DSU_IP + " STRING,"+ SQLiteInfo.DSU_PORT + " STRING,"
						+ SQLiteInfo.YW_SERVICE_IP + " STRING,"+SQLiteInfo.OPERATOR_MAC + " STRING)");
			}
		} catch (Exception e1) {
			log.debug("Create sqlite Exception:" + e1.getMessage());
		} finally {
			try {
				stat.close();
				conn.close();
			} catch (Exception e) {
				System.out.println("操作数据库发生异常" + e.getMessage());
			}
		}

	}
    /**
     * 新增设置
     */
	public void addSetting(SettingInfo info) {
		if(info == null) {
			return;
		}
		Connection conn = SqliteOperator.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("INSERT INTO " + SQLiteInfo.TABLE_NAME
					+ " VALUES(?,?,?,?,?,?,?,?,?)");
			ps.setString(1, info.getCenter_ip());
			ps.setString(2, info.getCenter_port());
			ps.setString(3, info.getLink_type());
			ps.setInt(4, info.getOpen_encryption());
			ps.setInt(5, info.getAgreement_type());
			ps.setString(6, info.getDSU_IP());
			ps.setString(7, info.getDSU_PORT());
			ps.setString(8, info.getYW_SERVICE_IP());
			ps.setString(9, info.getOperator_mac());
			ps.executeUpdate();
		} catch (Exception e) {
			log.debug("Insert Sqlite Exception:" + e.getMessage());
		} finally {
			try {
				ps.close();
				conn.close();
			} catch (Exception e) {
				System.out.println("e:" + e.getMessage());

			}
		}

	}
	/**
	 * 删除对象
	 * @param operatorIp:操作人的ip地址
	 */
	public void deleteSetting(String operatorIp) {
		if(StringUtils.isBlank(operatorIp)) {
			return;
		}
		Connection conn = SqliteOperator.getConnection();
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement("DELETE FROM " + SQLiteInfo.TABLE_NAME
					+ " WHERE " + SQLiteInfo.OPERATOR_MAC + "=?");
			ps.setString(1, operatorIp);
			ps.executeUpdate();
		} catch (Exception e) {
			log.debug("Delete Sqlite Exception:" + e.getMessage());
		} finally {
			try {
				ps.close();
				conn.close();
			} catch (Exception e) {
				System.out.println("e:" + e.getMessage());
			}
		}

	}


	public SettingInfo getSetting(String operatorIp) {
		SettingInfo settingInfo = new SettingInfo();
		Connection conn = SqliteOperator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("SELECT * FROM " + SQLiteInfo.TABLE_NAME
					+ " WHERE " + SQLiteInfo.OPERATOR_MAC + "=?");
			ps.setString(1, operatorIp);
			rs = ps.executeQuery();
			if (rs.next()) {
				settingInfo.setCenter_ip(rs.getString(SQLiteInfo.CENTER_IP));
				settingInfo.setCenter_port(rs.getString(SQLiteInfo.CENTER_PORT));
				settingInfo.setAgreement_type(rs.getInt(SQLiteInfo.AGREEMENT_TYPE));
				settingInfo.setDSU_IP(rs.getString(SQLiteInfo.DSU_IP));
				settingInfo.setDSU_PORT(rs.getString(SQLiteInfo.DSU_PORT));
				settingInfo.setYW_SERVICE_IP(rs.getString(SQLiteInfo.YW_SERVICE_IP));
				settingInfo.setLink_type(rs.getString(SQLiteInfo.LINK_TYPE));
				settingInfo.setOpen_encryption(rs.getInt(SQLiteInfo.OPEN_ENCRYPTION));
				settingInfo.setOperator_mac(rs.getString(SQLiteInfo.OPERATOR_MAC));
			}
			return settingInfo;
		} catch (Exception e) {
			log.debug("Select Sqlite Exception:" + e.getMessage());
		} finally {
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (Exception e) {
				System.out.println("e:" + e.getMessage());
				return null;
			}
		}
		return null;

	}

	public List<SettingInfo> getAllSettingList() {
		List<SettingInfo> telephones = new ArrayList<SettingInfo>();
		Connection conn = SqliteOperator.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement("SELECT * FROM " + SQLiteInfo.TABLE_NAME);

			rs = ps.executeQuery();
			while (rs.next()) {
				SettingInfo settingInfo = new SettingInfo();
				settingInfo.setCenter_ip(rs.getString(SQLiteInfo.CENTER_IP));
				settingInfo.setCenter_port(rs.getString(SQLiteInfo.CENTER_PORT));
				settingInfo.setAgreement_type(rs.getInt(SQLiteInfo.AGREEMENT_TYPE));
				settingInfo.setDSU_IP(rs.getString(SQLiteInfo.DSU_IP));
				settingInfo.setDSU_PORT(rs.getString(SQLiteInfo.DSU_PORT));
				settingInfo.setYW_SERVICE_IP(rs.getString(SQLiteInfo.YW_SERVICE_IP));
				settingInfo.setLink_type(rs.getString(SQLiteInfo.LINK_TYPE));
				settingInfo.setOpen_encryption(rs.getInt(SQLiteInfo.OPEN_ENCRYPTION));
				settingInfo.setOperator_mac(rs.getString(SQLiteInfo.OPERATOR_MAC));
				telephones.add(settingInfo);
			}
		} catch (Exception e) {
			log.debug("Select Sqlite Exception:" + e.getMessage());
		} finally {
			try {
				rs.close();
				ps.close();
				conn.close();
			} catch (Exception e) {
				System.out.println("e:" + e.getMessage());
			}
		}
		return telephones;
	}
	public boolean isTableExist(String tableName) {
		boolean exist = false;
		Connection conn = SqliteOperator.getConnection();
		ResultSet rs = null;
		DatabaseMetaData meta = null;
		try {
			meta = conn.getMetaData();
			rs = meta.getTables(null, null, tableName, null);
			if (rs.next()) {
				exist = true;
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("ͬe:" + e.getMessage());
		} finally {
			try {
				rs.close();
				conn.close();
			} catch (Exception e) {
				System.out.println("e:" + e.getMessage());
			}
		}

		return exist;
	}

}
